Alfaomega Duplicated Content From Marketplace

The tenant had already uploaded 232 publications from publisher Narcea, and then they arranged a deal with Narcea to have access to all their content. So those 232 publication where duplicated. This query lists all the duplicated content shared between these two tenants.

-- List duplicated content
SELECT
--  issues.external_id,
 --  tenant_issue.ti_id,
--  GROUP_CONCAT(tenant_issue.ti_id SEPARATOR ', ') AS tenant_issue_ti_ids,
 GROUP_CONCAT((
  CASE WHEN issues.tenant_id = 1305 THEN
   tenant_issue.ti_id
  WHEN issues.tenant_id = 341 THEN
   ''
  END) SEPARATOR '') AS tenant_issue_ti_ids_to_delete
--  tenant_issue.ti_tenant_id,
 --  tenant_issue.ti_issue_id,
--  GROUP_CONCAT(tenant_issue.ti_issue_id SEPARATOR ', ') AS tenant_issue_ti_issue_ids,
--  GROUP_CONCAT(terms.name SEPARATOR ', ') AS publishers,
--  COUNT(issues.external_id) AS repetitions
FROM
 tenant_issue
 JOIN issues ON issues.id = ti_issue_id
 JOIN taxables ON taxable_id = ti_issue_id
  AND taxable_type = 'issue'
 JOIN terms ON terms.id = taxables.term_id
  AND terms.taxonomy = 'publisher'
WHERE
 ti_tenant_id = 341
 --  AND issues.tenant_id = 1305
 AND issues.tenant_id IN(1305, 341)
 AND issues.deleted_at IS NULL
 -- This line is commented because the content is already "deleted"
--  AND tenant_issue.ti_deleted_at IS NULL
 AND terms.name IN('narcea', 'Narcea Ediciones')
GROUP BY
 issues.external_id
HAVING
 COUNT(issues.external_id) > 1;

Overview

---